﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace DBManager.AppCommon.Helper
{
        public class Param
        {

            public string ParamName;
            public object ParamValue;
            public Param(string prName, object prValue)
            {
                ParamName = prName;
                Type typeParam = prValue.GetType();
                ParamValue = prValue;
            }
        }

        public class SQLHelper
        {
            /// <summary>
            /// Cần gán giá trị cho ConnectionString trước khi sử dụng các method trong class này
            /// </summary>
            public static string ConnectionString = Common.GetConnectionString();

            /// <summary>
            /// Thực thi các thủ tục Insert, Update, Delete
            /// </summary>
            /// <param name="cmdText"></param>
            /// <param name="cmdType"></param>
            /// <param name="arrParam"></param>
            /// <returns>created by: nghiadt-29/09/2011</returns>
            public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params Param[] arrParam)
            {
                try
                {
                    using (SqlConnection cnn = new SqlConnection(ConnectionString))
                    {
                        SqlCommand cmd = cnn.CreateCommand();
                        cmd.CommandText = cmdText;
                        cmd.CommandType = cmdType;
                        if (arrParam != null)
                        {
                            foreach (Param pr in arrParam)
                            {
                                cmd.Parameters.AddWithValue(pr.ParamName, pr.ParamValue);
                            }
                        }
                        cnn.Open();
                        return cmd.ExecuteNonQuery();
                    }
                }
                catch
                {
                    throw;
                }
            }

            /// <summary>
            /// Thực thi truy vấn SLQ và trả về đối tượng SqlDataReader
            /// </summary>
            /// <param name="cmdText"></param>
            /// <param name="cmdType"></param>
            /// <param name="arrParam"></param>
            /// <returns>created by: nghiadt-29/11/2011</returns>
            public static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params Param[] arrParam)
            {
                try
                {
                    SqlConnection cnn = new SqlConnection(ConnectionString);
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = cmdText;
                    cmd.CommandType = cmdType;
                    if (arrParam != null)
                    {
                        foreach (Param pr in arrParam)
                        {
                            cmd.Parameters.AddWithValue(pr.ParamName, pr.ParamValue);
                        }
                    }
                    cnn.Open();
                    return cmd.ExecuteReader();
                }
                catch
                {
                    throw;
                }
            }


            /// <summary>
            /// Thực thi truy vấn SQL và trả về 1 giá trị duy nhất
            /// </summary>
            /// <param name="cmdText"></param>
            /// <param name="cmdType"></param>
            /// <param name="arrParam"></param>
            /// <returns>created by: nghiadt-29/11/2011</returns>
            public static object ExecuteScalar(string cmdText, CommandType cmdType, params Param[] arrParam)
            {
                try
                {
                    using (SqlConnection cnn = new SqlConnection(ConnectionString))
                    {
                        SqlCommand cmd = cnn.CreateCommand();
                        cmd.CommandText = cmdText;
                        cmd.CommandType = cmdType;
                        if (arrParam != null)
                        {
                            foreach (Param pr in arrParam)
                            {
                                cmd.Parameters.AddWithValue(pr.ParamName, pr.ParamValue);
                            }
                        }
                        cnn.Open();
                        return cmd.ExecuteScalar();
                    }
                }
                catch
                {
                    throw;
                }
            }

            /// <summary>
            /// Thực thi các thủ tục Select
            /// </summary>
            /// <typeparam name="T">Kiểu Entity</typeparam>
            /// <param name="cmdText"></param>
            /// <param name="cmdType"></param>
            /// <param name="arrParam"></param>
            /// <returns>Danh sách đối tượng T</returns>
            /// <remarks>created by: nghiadt-29/09/2011</remarks>
            public static List<T> GetEntities<T>(string cmdText, CommandType cmdType, params Param[] arrParam)
            {
                try
                {
                    DataSet objDataset = new DataSet();
                    using (SqlConnection cnn = new SqlConnection(ConnectionString))
                    {
                        SqlCommand cmd = cnn.CreateCommand();
                        cmd.CommandText = cmdText;
                        cmd.CommandType = cmdType;
                        if (arrParam != null)
                        {
                            foreach (Param pr in arrParam)
                            {
                                cmd.Parameters.AddWithValue(pr.ParamName, pr.ParamValue);
                            }
                        }

                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        adapter.Fill(objDataset);
                    }

                    List<T> result = new List<T>();//Danh sách trả về
                    if (objDataset.Tables.Count > 0)
                    {
                        PropertyInfo[] propInfos = typeof(T).GetProperties(); //Lấy các thuộc tính của đối tượng (Entities)
                        foreach (DataRow objDataRow in objDataset.Tables[0].Rows)
                        {
                            T obj = System.Activator.CreateInstance<T>(); //Tạo thể hiện của đối tượng
                            foreach (PropertyInfo propInfo in propInfos)
                            {
                                if (objDataset.Tables[0].Columns.Contains(propInfo.Name) && (objDataRow[propInfo.Name] != null) && (objDataRow[propInfo.Name] != DBNull.Value))
                                {
                                    propInfo.SetValue(obj, objDataRow[propInfo.Name], null);
                                }
                            }
                            result.Add(obj);
                        }
                    }
                    return result;
                }
                catch
                {
                    throw;
                }
            }

            /// <summary>
            /// Thực thi các thủ tục Select
            /// </summary>
            /// <typeparam name="T">Kiểu Entity</typeparam>
            /// <param name="cmdText"></param>
            /// <param name="cmdType"></param>
            /// <param name="arrParam"></param>
            /// <returns>Đối tượng T</returns>
            /// <remarks>created by: nghiadt-29/09/2011</remarks>
            public static T GetEntity<T>(string cmdText, CommandType cmdType, params Param[] arrParam)
            {
                try
                {
                    DataSet objDataset = new DataSet();
                    using (SqlConnection cnn = new SqlConnection(ConnectionString))
                    {
                        SqlCommand cmd = cnn.CreateCommand();
                        cmd.CommandText = cmdText;
                        cmd.CommandType = cmdType;
                        if (arrParam != null)
                        {
                            foreach (Param pr in arrParam)
                            {
                                cmd.Parameters.AddWithValue(pr.ParamName, pr.ParamValue);
                            }
                        }

                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.SelectCommand = cmd;
                        adapter.Fill(objDataset);
                    }

                    T result = default(T); //Đối tượng trả về
                    if (objDataset.Tables.Count > 0 && objDataset.Tables[0].Rows.Count > 0)
                    {
                        result = System.Activator.CreateInstance<T>();
                        PropertyInfo[] propInfos = typeof(T).GetProperties(); //Lấy các thuộc tính của đối tượng (Entities)
                        DataRow objDataRow = objDataset.Tables[0].Rows[0];
                        foreach (PropertyInfo propInfo in propInfos)
                        {
                            if (objDataset.Tables[0].Columns.Contains(propInfo.Name) && (objDataRow[propInfo.Name] != null) && (objDataRow[propInfo.Name] != DBNull.Value))
                            {
                                propInfo.SetValue(result, objDataRow[propInfo.Name], null);
                            }
                        }
                    }
                    return result;
                }
                catch
                {
                    throw;
                }
            }
        }
}
